# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
# get_project_settings()方法导入settings中的数据，记住此导入方法就行了，或者按照步骤去选
from scrapy.utils.project import get_project_settings

# 此管道用于下载数据
class ScrapyReadbook36Pipeline:

    def open_spider(self,spider):
        self.fp = open('book.json','w',encoding='utf-8')

    def process_item(self, item, spider):
        self.fp.write(str(item))
        return item

    def close_spider(self,spider):
        self.fp.close()

# 添加个管道，用于存入数据库
class mysqlPipeline:

    def open_spider(self,spider):
        # 获取到settings中的数据库参数
        settings = get_project_settings()
        self.host = settings['DB_HOST']
        self.port = settings['DB_PORT']
        self.user = settings['DB_USER']
        self.password = settings['DB_PASSWORD']
        self.database = settings['DB_DATABASE']
        self.charset = settings['DB_CHARSET']

        # 添加个连接数据库的函数
        self.connect()

    def connect(self):
        # 建立数据库连接
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset
        )
        # 创建游标
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):

        # 使用format格式化字符串，将数据传入sql语句中,format使用{}代表传入的数据的位置
        sql = 'insert into readbook(name,src) values("{}","{}")'.format(item['name'],item['src'])
        self.cursor.execute(sql)

        self.conn.commit()

        return item

    def close_spider(self,spider):

        self.cursor.close()
        self.conn.close()
